package com.navi.loaddata.loadService.impl.SpcDc2EdcConf;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.gpEntity.EdcConfig;
import com.navi.loaddata.core.gpEntity.EdcConfigModel;
import com.navi.loaddata.core.gpEntity.SpcDcdata;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by ghost on 2018/4/18.
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class SpcDc2EdcConfService {
    @Autowired
    GreenPlumRepositoryImpl gpDao;

    private static final Logger logger = LoggerFactory.getLogger(SpcDc2EdcConfService.class);

    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());

    public int spcDc2EdcConfMain(EtlConfD etlConfD) throws ETLException {
        int countSize = 0;
        Session session = gpDao.getEntityManagerSession();
        try {
            countSize = spcDc2EdcConf(session, etlConfD);
            logger.info("LoadSpcDc2EdcConfDataMain finish");
        } catch (ETLException e) {
            throw new ETLException(e.getMessage());
        }
        return countSize;
    }


    public int spcDc2EdcConf(Session session, EtlConfD etlConfD) throws ETLException {
        int countSize = 0;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());
        Timestamp nowTime = Timestamp.valueOf(LocalDateTime.now());

        List<SpcDcdata> spcDcdataList = new ArrayList<>();
        HashMap<String, List<EdcConfigModel>> edcConfigModelMap = new HashMap<>();

//            String spcDcSql="from SpcDcdata where dbTimestamp>='"+etlConfD.getRunStartTimestamp()+"' and dbTimestamp<'"+etlConfD.getRunEndTimestamp()+"'";
//            spcDcdataList=gpDao.find(SpcDcdata.class,spcDcSql);
        try {
            //GetedcData
            QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
            String edcConfSql = "SELECT " +
                    " a.SUBFACTORYNAME AS  shopId," +
                    " a.MACHINENAME AS lineId," +
                    " a.UNITNAME AS subeqId," +
                    " b.ITEMNAME AS itemName," +
                    " b.SITENAME AS siteName" +
                    " FROM SPC_DCDATA a" +
                    " LEFT JOIN (SELECT DCDATAID,ITEMNAME,SITENAME FROM SPC_DCMEASUREDATARESULT WHERE ITEMNAME in('OLX_1','OLY_1') ) b ON a.DCDATAID=b.DCDATAID " +
                    " WHERE a.MACHINENAME in('SAMP0400','SAMP0500')" +
                    " AND a.TIMEKEY>='" + startTime + "'" +
                    " AND a.TIMEKEY<'" + endTime + "'" +
                    " AND a.PRODUCTSPECNAME IS NOT NULL " +
                    " AND b.ITEMNAME IS NOT NULL" +
                    " AND b.SITENAME>='001' AND b.SITENAME <='048' " +
                    " GROUP BY a.SUBFACTORYNAME,a.MACHINENAME,a.UNITNAME,b.ITEMNAME,b.SITENAME" +
                    " order by a.MACHINENAME,b.ITEMNAME,b.SITENAME";
//                    " group by a.unit_eqpt_id,b.itemname,b.sitename";
            List<EdcConfigModel> EdcConfigModelList = qr_mes.query(edcConfSql, new BeanListHandler<EdcConfigModel>(EdcConfigModel.class));
            logger.info("EdcConfig数量:{}", EdcConfigModelList.size());
            //按subeq分组
            for (EdcConfigModel edc : EdcConfigModelList) {
                if (null == edcConfigModelMap.get(edc.getSubeqId())) {
                    List<EdcConfigModel> edcConfList = new ArrayList<>();
                    edcConfList.add(edc);
                    edcConfigModelMap.put(edc.getSubeqId(), edcConfList);
                } else {
                    edcConfigModelMap.get(edc.getSubeqId()).add(edc);
                }
            }

            //insert GroupBy subeq
            for (String key : edcConfigModelMap.keySet()) {
                //判断是否存在表,不存在就建表 "edc_data_"+edcConfigModel.getSubeqId()
                String findTableSql = "SELECT count(0) FROM information_schema.TABLES WHERE table_name ='edc_data_" + StringUtils.swapCase(key) + "'";
                String createTableSql;
                Object obj= gpDao.findBySQL(findTableSql).get(0);
                int countSubeqTable=Integer.parseInt(obj.toString());
                if (0==countSubeqTable) {
                    logger.error("表名:" + "edc_data_" + key + " 不存在!请联系管理员建表:CREATE TABLE edc_data_"+StringUtils.swapCase(key)+" (LIKE edc_data_pipr0302 INCLUDING INDEXES);");
                    createTableSql = "CREATE TABLE edc_data_"+StringUtils.swapCase(key)+" (LIKE edc_data_pipr0302 INCLUDING INDEXES);";
                    SQLQuery query = session.createSQLQuery(createTableSql);
                    query.executeUpdate();
                    logger.info("新建表：" + "edc_data_" + key + " 成功！");
                }


                logger.info("inserting into edc_config... subeqId={}", key);
                int resultMax = 1;
                String resultCol = "";
                //判断是否存在subeq,itemname,sitename
                List<EdcConfig> existEdcConfList = gpDao.find(EdcConfig.class, "from EdcConfig where subeqId='" + key + "'");
                HashMap<String, String> existEdcConfMap = new HashMap<>();


                //如果存在subeq,放到existEdcConfMap
                if (null != existEdcConfList && existEdcConfList.size() > 0) {
                    for (EdcConfig e : existEdcConfList) {
                        existEdcConfMap.put(e.getShopId() + "_" + e.getSubeqId() + "_" + e.getItemName() + "_" + e.getSiteName(), e.getShopId());
                        int resNum = Integer.valueOf(e.getResultColumn().split("_")[1]);
                        //找最大的result_column
                        if (resultMax < resNum) {
                            resultMax = resNum;
                        }
                    }
                }


                StringBuffer insertSQL = new StringBuffer();
                insertSQL.append("insert into edc_config (edc_config_key,shop_id,line_id,ope_id,subeq_id,item_name,site_name,site_type,result_column,row_no,del_flg,evt_user,evt_timestamp)  values");

                for (EdcConfigModel edc : edcConfigModelMap.get(key)) {
                    if (null != existEdcConfMap && existEdcConfMap.size() > 0 && null != existEdcConfMap.get(edc.getShopId() + "_" + edc.getSubeqId() + "_" + edc.getItemName() + "_" + edc.getSiteName())) {
//                        logger.info("已经存在subeqId={},itemName={},siteName={}", edc.getSubeqId(), edc.getItemName(), edc.getSiteName());
                        continue;
                    }
                    resultMax = resultMax + 1;
                    if (String.valueOf(resultMax).length() == 1) {
                        resultCol = "00" + resultMax;
                    } else if (String.valueOf(resultMax).length() == 2) {
                        resultCol = "0" + resultMax;
                    } else if (String.valueOf(resultMax).length() == 3) {
                        if(resultMax>=500){
                            throw new ETLException("子机台:" + key + "result_column数超过500.请联系管理员处理");
                        }
                        resultCol = String.valueOf(resultMax);
                    } else {
                        logger.error("result_column数超过1000.");
                        throw new ETLException("子机台:" + key + "result_column数超过500.请联系管理员处理");
                    }
                    insertSQL.append(" (nextval('edc_config_key_seq')," +
                            "'" + edc.getShopId() + "'," +
                            "'" + edc.getLineId() + "'," +
                            " " + null + "," +
                            "'" + edc.getSubeqId() + "'," +
                            "'" + edc.getItemName() + "'," +
                            "'" + edc.getSiteName() + "'," +
                            " " + null + "," +
                            "'result_" + resultCol + "'," +//column
                            "'1'," +
                            "'N'," +
                            "'ETL'," +
                            "'" + nowTime.toString() + "'),");
                }
                if (StringUtils.endsWith(insertSQL.toString(), "values")) {
                    logger.info("subeqId={}在此时间段不存在需要新增的edc_config数据。",key);
                    continue;
                }
                insertSQL.deleteCharAt(insertSQL.length() - 1);
                insertSQL.append(";");
                SQLQuery query = session.createSQLQuery(insertSQL.toString());
                query.executeUpdate();
                countSize++;
            }
//        } catch (SQLException e) {
//            logger.error(e.getMessage());
//            loggerUtil.printStackTrace(e);
//            throw new ETLException("同步EDCConfig失败:" + e.getMessage());
        } catch (Exception e) {
            logger.error(e.getMessage());
            loggerUtil.printStackTrace(e);
            throw new ETLException("同步EDCConfig失败:" + e.getMessage());
        }
        return countSize;
    }

}
